---
sidebar_label: Run SQL
sidebar_position: 1
description: Execute SQL with the Hasura schema/Metadata API
keywords:
  - hasura
  - docs
  - schema/Metadata API
  - API reference
  - run_sql
---

# Schema API Reference: Run SQL (v2.0 and above)

## run_sql {#schema-run-sql}

`run_sql` can be used to run arbitrary SQL statements.

Multiple SQL statements can be separated by a `;`, however, only the
result of the last SQL statement will be returned.

:::info Admin-only

This is an admin-only request, i.e. the request can only be executed
with `X-Hasura-Role: admin`. This can be set by passing
`X-Hasura-Admin-Secret` or by setting the right role in webhook/JWT
authorization mode.

This is deliberate as it is hard to enforce any sort of permissions on
arbitrary SQL. If you find yourself in the need of using `run_sql` to
run custom DML requests, consider creating a view. You can now define
permissions on that particular view for various roles.

:::

:::tip Supported from

The schema API is supported for versions `v2.0.0` and above and replaces
the older [schema/Metadata API](/api-reference/schema-metadata-api/index.mdx).

:::

### Supported databases

`run_sql` can be invoked on most supported databases, but typically it requires
a prefix corresponding to that database.

In order to use it, you will need to specify the correct `type` in the query, as
below:

| Database      | `type` parameter              |
| ------------- | ----------------------------- |
| Postgres      | `"run_sql"` or `"pg_run_sql"` |
| MS SQL Server | `"mssql_run_sql"`             |
| Citus         | `"citus_run_sql"`             |
| CockroachDB   | `"cockroach_run_sql"`         |
| BigQuery      | `"bigquery_run_sql"`          |

When using a data connector, use the connector's prefix.

All examples below will use the Postgres `type` parameter, `"run_sql"`.

### Use cases

1.  To execute DDL operations that are not supported by the Console
    (e.g. managing indexes).
2.  Run custom DML requests from backend microservices instead of
    installing libraries to speak to Postgres.

An example:

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
    "type": "run_sql",
    "args": {
        "source": "default",
        "sql": "CREATE UNIQUE INDEX ON films (title);"
    }
}
```

While `run_sql` lets you run any SQL, it tries to ensure that the Hasura
GraphQL Engine's state (relationships, permissions etc.) is consistent
i.e. you cannot drop a column on which any Metadata is dependent on (say
a permission or a relationship). The effects, however, can be cascaded.

Example: If we were to drop the 'bio' column from the author table
(let's say the column is used in some permission), you would see an
error.

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
    "type": "run_sql",
    "args": {
        "source": "default",
        "sql": "ALTER TABLE author DROP COLUMN name"
    }
}
```

```http
HTTP/1.1 400 BAD REQUEST
Content-Type: application/json

{
    "path": "$.args",
    "error": "cannot drop due to the following dependent objects: permission author.user.select"
}
```

We can however, cascade these changes.

```http {9}
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
    "type": "run_sql",
    "args": {
        "source": "default",
        "sql": "ALTER TABLE author DROP COLUMN bio",
        "cascade" : true
    }
}
```

```http
HTTP/1.1 200 OK
Content-Type: application/json

{
    "result_type": "CommandOk"
}
```

With the above request, the dependent permission is also dropped.

Example: If we were to drop a foreign key constraint from the article
table (let's say the column involved in the foreign key is used to
define a relationship), you would see an error.

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
    "type": "run_sql",
    "args": {
        "source": "default",
        "sql": "ALTER TABLE article DROP CONSTRAINT article_author_id_fkey"
    }
}
```

```http
HTTP/1.1 400 BAD REQUEST
Content-Type: application/json

{
    "path": "$.args",
    "error": "cannot drop due to the following dependent objects: constraint article.article_author_id_fkey"
}
```

We can however, cascade these changes.

```http {9}
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
    "type": "run_sql",
    "args": {
        "source": "default",
        "sql": "ALTER TABLE article DROP CONSTRAINT article_author_id_fkey",
        "cascade" : true
    }
}
```

```http
HTTP/1.1 200 OK
Content-Type: application/json

{
    "result_type": "CommandOk"
}
```

With the above request, the dependent permission is also dropped.

In general, the SQL operations that will affect Hasura Metadata are:

1.  Dropping columns
2.  Dropping tables
3.  Dropping foreign keys
4.  Altering types of columns
5.  Dropping SQL functions
6.  Overloading SQL functions

In case of 1, 2 and 3 the dependent objects (if any) can be dropped
using `cascade`. However, when altering type of columns, if any objects
are affected, the change cannot be cascaded. So, those dependent objects
have to be manually dropped before executing the SQL statement. Dropping
SQL functions will cascade the functions in Metadata even without using
`cascade` since no other objects depend on them. Overloading tracked SQL
functions is not allowed.

Set `check_metadata_consistency` field to `false` to force the server to
not consider Metadata dependencies.

### Args syntax {#schema-run-sql-syntax}

| Key                          | Required | Supported databases        | Schema  | Description                                                                                                                                                                                                                        |
|------------------------------|----------|----------------------------|---------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| `sql`                        | true     | _all_                      | String  | The sql to be executed                                                                                                                                                                                                             |
| `source`                     | false    | _all_                      | String  | The database on which the sql is to be executed (default: 'default' database)                                                                                                                                                      |
| `cascade`                    | false    | Postgres and MS SQL Server | Boolean | When set to `true`, the effect (if possible) is cascaded to any hasuradb dependent objects (relationships, permissions, templates).                                                                                                |
| `check_metadata_consistency` | false    | Postgres and MS SQL Server | Boolean | When set to `false`, the sql is executed without checking Metadata dependencies.                                                                                                                                                   |
| `read_only`                  | false    | Postgres                   | Boolean | When set to `true`, the request will be run in `READ ONLY` transaction access mode which means only `select` queries will be successful. This flag ensures that the GraphQL schema is not modified and is hence highly performant. |
| `no_transaction`             | false    | Postgres                   | Boolean | When set to `true`, statements are executed outside transaction blocks. Useful for operations like `CREATE INDEX CONCURRENTLY` that cannot run within transactions (default: `false`)                                              |

### no_transaction flag

The `no_transaction` flag controls how SQL statements are executed:

- When `false` (default):
  - Statements are executed within a transaction block
  - Multiple statements are executed as a single batch
  - If any statement fails, all changes are rolled back
  - Provides ACID guarantees
  - Will fail for operations that cannot run in transactions (e.g. `CREATE INDEX CONCURRENTLY`)

- When `true`:
  - Statements are executed outside any transaction block
  - Multiple statements are split and executed individually
  - If a statement fails, previous successful statements are not rolled back
  - Returns result of the last successful statement
  - Allows running operations that cannot be executed in transactions

Example using `no_transaction`:

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
    "type": "run_sql",
    "args": {
        "source": "default",
        "sql": "CREATE INDEX CONCURRENTLY idx_user_email ON users(email);",
        "no_transaction": true
    }
}
```

:::info Multiple Statements

By default, PostgreSQL executes multiple SQL statements (separated by semicolons) as a single transaction, even when not explicitly wrapped in a transaction block.
The `no_transaction` flag overcomes this limitation by executing each statement independently, allowing operations that cannot run within transactions.
See [PostgreSQL docs](https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-MULTI-STATEMENT) for more details.
:::

### Response

The response is a JSON Object with the following structure.

| Key         | Always present | Schema                                                      | Description                                               |
| ----------- | -------------- | ----------------------------------------------------------- | --------------------------------------------------------- |
| result_type | true           | String                                                      | One of "CommandOk" or "TuplesOk"                          |
| result      | false          | `[[Text]]` (An array of rows, each row an array of columns) | This is present only when the `result_type` is "TuplesOk" |

:::info Note

The first row in the `result` (when present) will be the names of the
columns.

:::

### Some examples

An SQL query returning results.

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
    "type": "run_sql",
    "args": {
        "source": "default",
        "sql": "select user_id, first_name from author limit 2;"
    }
}
```

```http
HTTP/1.1 200 OK
Content-Type: application/json

{
    "result_type": "TuplesOk",
    "result": [
        [
            "user_id",
            "first_name"
        ],
        [
            "1",
            "andre"
        ],
        [
            "2",
            "angela"
        ]
    ]
}
```

An SQL query to create a table:

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type":"run_sql",
  "args": {
    "source": "default",
    "sql": "create table item ( id serial,  name text,  category text,  primary key (id))",
    "check_metadata_consistency": false
  }
}
```

```http
HTTP/1.1 200 OK
Content-Type: application/json

{
  "result_type": "CommandOk",
  "result": null
}
```
